﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Classes;
namespace FlightSystemDBLayer
{
    public class DBCity : ICity
    {
        private static SqlCommand cmd = null;
        private string c;


        public DBCity()
        {

        }

        public  List<City> GetDBCities()
        {
            List<City> List = new List<City>();
            string sql = "select * from City";
            cmd = SQLAccess.GetDbCommand(sql);

            IDataReader dbReader;
            dbReader = cmd.ExecuteReader();

            City a;

            while (dbReader.Read())
            {
                a = new City(dbReader["Name"].ToString(), dbReader["Country"].ToString());
                List.Add(a);
            }
            SQLAccess.Close();
            return List;

        }
        public  void CreateCity(string name, string country, string airport)
        {
            string sql = @"insert into City values (" + name + ", " + country + ", " + airport + ",)";
            SqlCommand cmd = SQLAccess.GetDbCommand(sql);
            cmd.ExecuteNonQuery();
            SQLAccess.Close();
        }

        public City FindCityByName(string name)
        {
            string sql = @"select * from City where name = " + name;
            cmd = SQLAccess.GetDbCommand(sql);

            IDataReader dbReader;
            dbReader = cmd.ExecuteReader();

            City a;

            if (dbReader.Read())
            {
                a = new City(name, dbReader["airport"].ToString());
            }
            else
            {
                a = null;
                dbReader.Close();
                SQLAccess.Close();

            }
            return a;
        }

        public City FindCityByID(int IDCity)
        {
            string sql = @"select * from City where IDCity = " + IDCity;
            cmd = SQLAccess.GetDbCommand(sql);

            IDataReader dbReader;
            dbReader = cmd.ExecuteReader();

            City a;
            if (dbReader.Read())
            {

                a = new City(dbReader["name"].ToString(), dbReader["Country"].ToString());
            }
            else
            {
                a = null;
                dbReader.Close();
                SQLAccess.Close();

            }
            return a;
        }
    }
}